package com.tz.farmer.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.tz.farmer.model.CheckApply;
import com.tz.farmer.model.FarmerKnowledge;
import com.tz.farmer.vo.StatisticsVo;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

public interface CheckApplyMapper extends BaseMapper<CheckApply> {
    int deleteByPrimaryKey(Integer id);

    int insert(CheckApply record);

    int insertSelective(CheckApply record);

    CheckApply selectByPrimaryKey(Integer id);

    int updateByPrimaryKeySelective(CheckApply record);

    int updateByPrimaryKey(CheckApply record);

    /**
     * kai
     * 农|商户 初始化
     * @return
     */
    @Select("<script>" +
            "SELECT\n" +
            "\tc.id,\n" +
            "\tcount( check_name ) AS count,\n" +
            "\tc.upload_time,\n" +
            "\tb.business_name, \n" +
            "\tg.goods_name \n" +
            "FROM\n" +
            "\tcheck_apply AS c\n" +
            "\tLEFT JOIN business_info AS b ON c.check_name = b.unisc_id \n" +
            "\tLEFT JOIN goods_info AS g ON g.goods_id = c.goods_id \n" +
            "WHERE\n" +
            "\tc.property = #{property} \n" +
            "\tAND check_type = #{type} and 1=1 \n" +
            "   <if test=\"startTime != null and startTime.trim() != ''\">" +
            "   <![CDATA[   and ( STR_TO_DATE(upload_time, '%Y-%m-%d %H:%i:%s') >=  STR_TO_DATE( '${startTime} 00:00:00', '%Y-%m-%d %H:%i:%s')   \n" +
            "      and STR_TO_DATE(upload_time, '%Y-%m-%d %H:%i:%s') <= STR_TO_DATE( '${endTime} 23:59:59', '%Y-%m-%d %H:%i:%s')  )  ]]> \n" +
            " </if> " +
            "GROUP BY\n" +
            "\tcheck_name, c.id,c.upload_time,b.business_name,g.goods_name \n" +
            "ORDER BY\n" +
            "\tcount DESC \n" +
            "\tLIMIT 10" +
            "</script>")
    List<StatisticsVo> selectBusiness(@Param("property") Integer property,@Param("type") String type,@Param("startTime")String startTime ,@Param("endTime")String endTime);


    /**
     * kai
     * 农|商 产品 初始化
     * @param property
     * @param type
     * @return
     */
    @Select("<script>" +
            "SELECT \n" +
            "             c.id, \n" +
            "             count( check_name ) AS count, \n" +
            "             c.upload_time, \n" +
            "             g.goods_name , \n" +
            "             b.business_name  \n" +
            "             FROM \n" +
            "             check_apply AS c \n" +
            "             LEFT JOIN goods_info AS g ON c.goods_id = g.goods_id  \n" +
            "             LEFT JOIN business_info AS b ON c.check_name = b.unisc_id \n" +
            "             WHERE \n" +
            "             c.property = #{property} \n" +
            "             AND check_type = '当前产品' and 1=1  \n" +
            "   <if test=\"startTime != null and startTime.trim() != ''\">" +
            "   <![CDATA[   and ( STR_TO_DATE(upload_time, '%Y-%m-%d %H:%i:%s') >=  STR_TO_DATE( '${startTime} 00:00:00', '%Y-%m-%d %H:%i:%s')   \n" +
            "      and STR_TO_DATE(upload_time, '%Y-%m-%d %H:%i:%s') <= STR_TO_DATE( '${endTime} 23:59:59', '%Y-%m-%d %H:%i:%s')  )  ]]> \n" +
            " </if> " +
            "             GROUP BY \n" +
            "             check_name,c.id,c.upload_time,g.goods_name,b.business_name  \n" +
            "             ORDER BY \n" +
            "             count DESC  \n" +
            "             LIMIT 10"+
            "</script>")
    List<StatisticsVo> selectProduct(@Param("property") Integer property,@Param("type") String type,@Param("startTime")String startTime ,@Param("endTime")String endTime);



    /**
     * kai 总条数
     * @return
     */
    @Select("<script>" +
            "select   \n" +
            "count(1) \n" +
            "from check_apply \n" +
            "where property = #{flag} and 1 = 1  " +
            " <if test = \"keyword != null and keyword.trim() !=''  \"> and ( name like CONCAT('%', #{keyword},'%')  \n" +
            "    or phone like CONCAT('%', #{keyword},'%') )</if> \n" +
            " <if test = \"classify != null and classify.trim() !=''  \" > and check_type like CONCAT('%', #{classify},'%')  </if> \n" +
            " <if test=\"startTime != null and startTime.trim() != ''\">" +
            "   <![CDATA[   and  ( STR_TO_DATE(upload_time, '%Y-%m-%d %H:%i:%s') >=  STR_TO_DATE( '${startTime} 00:00:00', '%Y-%m-%d %H:%i:%s')   \n" +
            "     and STR_TO_DATE(upload_time, '%Y-%m-%d %H:%i:%s') <= STR_TO_DATE( '${endTime} 23:59:59', '%Y-%m-%d %H:%i:%s')  )  ]]> \n" +
            " </if> " +
            "</script>")
    int countFlag(@Param("flag") Integer flag ,@Param("classify") String classify, @Param("keyword") String keyword ,@Param("startTime")String startTime ,@Param("endTime")String endTime);

    /**
     * kai 条件查询
     * @return
     */
    @Select("<script>" +
            "select   \n" +
            " id, \n" +
            " check_type, \n" +
            " check_content, \n" +
            " `name`, \n" +
            " sex, \n" +
            " phone, \n" +
            " photo, \n" +
            " property, \n" +
            " upload_time, \n" +
            " check_name,\n" +
            " goods_id\n" +
            " FROM\n" +
            " check_apply" +
            " where property = #{flag} and 1 = 1  " +
            " <if test = \"keyword != null and keyword.trim() !=''  \" > and ( name like CONCAT('%', #{keyword},'%')  \n" +
            "  or phone like CONCAT('%', #{keyword},'%') )</if> \n" +
            " <if test = \"classify != null and classify.trim() !=''  \" > and check_type like CONCAT('%', #{classify},'%')  </if> \n" +
            " <if test=\"startTime != null and startTime.trim() != ''\">" +
            "   <![CDATA[   and ( STR_TO_DATE(upload_time, '%Y-%m-%d %H:%i:%s') >=  STR_TO_DATE( '${startTime} 00:00:00', '%Y-%m-%d %H:%i:%s')    \n" +
            "       and STR_TO_DATE(upload_time, '%Y-%m-%d %H:%i:%s') <= STR_TO_DATE( '${endTime} 23:59:59', '%Y-%m-%d %H:%i:%s')   ) ]]> \n" +
            " </if> " +
            "  order by upload_time DESC limit #{n} , #{m} " +
            "</script>")
    List<CheckApply> selectConditionList(@Param("flag") Integer flag ,@Param("classify") String classify, @Param("keyword") String keyword ,@Param("startTime")String startTime ,@Param("endTime")String endTime, @Param("n")int n, @Param("m")int m);
    /**
     * kai 条件查询
     * @return
     */
    @Select("<script>" +
            "select   \n" +
            " id, \n" +
            " check_type, \n" +
            " check_content, \n" +
            " `name`, \n" +
            " sex, \n" +
            " phone, \n" +
            " photo, \n" +
            " property, \n" +
            " upload_time, \n" +
            " check_name,\n" +
            " goods_id\n" +
            " FROM\n" +
            " check_apply" +
            " where property = #{flag} and 1 = 1  " +
            " <if test = \"keyword != null and keyword.trim() !=''  \" > and ( name like CONCAT('%', #{keyword},'%') \n" +
            "  or phone like CONCAT('%', #{keyword},'%') ) </if> \n" +
            " <if test = \"classify != null and classify.trim() !=''  \"> and check_type like CONCAT('%', #{classify},'%')  </if> \n" +
            " <if test=\"startTime != null and startTime.trim() != ''\">" +
            "   <![CDATA[   and ( STR_TO_DATE(upload_time, '%Y-%m-%d %H:%i:%s') >=  STR_TO_DATE( '${startTime} 00:00:00', '%Y-%m-%d %H:%i:%s')    \n" +
            "       and STR_TO_DATE(upload_time, '%Y-%m-%d %H:%i:%s') <= STR_TO_DATE( '${endTime} 23:59:59', '%Y-%m-%d %H:%i:%s')  )  ]]> \n" +
            " </if> " +
            " <if test = 'ids != null and ids.length !=0 ' > and id in  \n" +
            "   <foreach collection='ids' item='ids' open=\"(\" separator=\",\" close=\")\">\n" +
            "        #{ids}\n" +
            "        </foreach>"+
            "         </if>"+
            "  order by upload_time DESC  " +
            "</script>")
    List<CheckApply> selectDownLoadList(@Param("flag") Integer flag ,@Param("classify") String classify, @Param("keyword") String keyword ,@Param("startTime")String startTime ,@Param("endTime")String endTime,@Param("ids")Integer[] ids);


}